from flask import Blueprint, render_template, request, redirect, url_for, session, flash, jsonify, make_response
from models import db, TradeModel, CardModel
from sqlalchemy import or_, func, and_, desc
import csv
from io import StringIO
from datetime import datetime

admintrade_bp = Blueprint('admintrade', __name__, url_prefix='/admintrade')


@admintrade_bp.route('/admintrade', methods=['GET', 'POST'])
def admintrade():
    # 获取搜索参数
    cardIDSearch = request.args.get('cardIDSearch', '')
    tradeTypeSearch = request.args.get('tradeTypeSearch', '')
    tradeDateSearch = request.args.get('tradeDateSearch', '')

    # 获取分页参数
    page = request.args.get('page', 1, type=int)
    per_page = 10

    # 处理表单提交
    if request.method == 'POST':
        action = request.form.get('action')

        if action == 'add':
            # 添加新交易
            cardID = request.form.get('cardID')
            tradeType = request.form.get('tradeType')
            tradeMoney = float(request.form.get('tradeMoney'))
            remark = request.form.get('remark')

            # 验证卡号是否存在
            card = CardModel.query.filter_by(cardID=cardID).first()
            if not card:
                flash('银行卡号不存在', 'danger')
                return redirect(url_for('admintrade.admintrade'))

            # 验证卡是否挂失
            if card.IsReportLoss == '是':
                flash('该银行卡已挂失，不能进行交易', 'danger')
                return redirect(url_for('admintrade.admintrade'))

            # 处理转账
            if tradeType == '转账':
                targetCardID = request.form.get('targetCardID')
                targetCard = CardModel.query.filter_by(cardID=targetCardID).first()

                if not targetCard:
                    flash('目标银行卡号不存在', 'danger')
                    return redirect(url_for('admintrade.admintrade'))

                if targetCard.IsReportLoss == '是':
                    flash('目标银行卡已挂失，不能进行交易', 'danger')
                    return redirect(url_for('admintrade.admintrade'))

                # 调用存储过程执行转账
                try:
                    # 调用存储过程
                    result = db.session.execute(
                        "CALL TransferFunds(:fromCard, :toCard, :amount, :remark)",
                        {
                            'fromCard': cardID,
                            'toCard': targetCardID,
                            'amount': tradeMoney,
                            'remark': remark
                        }
                    )
                    db.session.commit()
                    flash('转账成功', 'success')
                except Exception as e:
                    db.session.rollback()
                    flash(f'转账失败: {str(e)}', 'danger')

            else:
                # 处理存款/取款
                new_trade = TradeModel(
                    cardID=cardID,
                    tradeType=tradeType,
                    tradeMoney=tradeMoney,
                    remark=remark
                )
                db.session.add(new_trade)
                db.session.commit()
                flash(f'{tradeType}操作成功', 'success')

            return redirect(url_for('admintrade.admintrade'))

        elif action == 'delete':
            # 删除交易
            tradeID = request.form.get('tradeID')
            trade = TradeModel.query.get(tradeID)
            if trade:
                db.session.delete(trade)
                db.session.commit()
                flash('交易记录删除成功', 'success')
            else:
                flash('交易记录不存在', 'danger')
            return redirect(url_for('admintrade.admintrade'))

        elif action == 'export_csv':
            # 导出CSV
            trades = get_filtered_trades(cardIDSearch, tradeTypeSearch, tradeDateSearch)

            si = StringIO()
            cw = csv.writer(si)
            cw.writerow(['交易ID', '卡号', '交易时间', '交易类型', '交易金额', '备注'])

            for trade in trades:
                cw.writerow([
                    trade.tradeID,
                    trade.cardID,
                    trade.tradeDate.strftime('%Y-%m-%d %H:%M:%S'),
                    trade.tradeType,
                    trade.tradeMoney,
                    trade.remark or ''
                ])

            output = make_response(si.getvalue())
            output.headers["Content-Disposition"] = "attachment; filename=trades.csv"
            output.headers["Content-type"] = "text/csv"
            return output

    # 获取过滤后的交易
    trades_query = get_filtered_trades(cardIDSearch, tradeTypeSearch, tradeDateSearch)

    # 使用 paginate 方法获取分页对象
    trades = trades_query.order_by(desc(TradeModel.tradeDate)).paginate(
        page=page,
        per_page=per_page,
        error_out=False
    )

    return render_template('admin_main_trade.html',
                           trades=trades,
                           cardIDSearch=cardIDSearch,
                           tradeTypeSearch=tradeTypeSearch,
                           tradeDateSearch=tradeDateSearch)


def get_filtered_trades(cardIDSearch, tradeTypeSearch, tradeDateSearch):
    # 构建查询
    query = TradeModel.query

    if cardIDSearch:
        query = query.filter(TradeModel.cardID.like(f'%{cardIDSearch}%'))

    if tradeTypeSearch:
        query = query.filter(TradeModel.tradeType == tradeTypeSearch)

    if tradeDateSearch:
        try:
            trade_date = datetime.strptime(tradeDateSearch, '%Y-%m-%d')
            next_day = trade_date.replace(day=trade_date.day + 1)
            query = query.filter(and_(TradeModel.tradeDate >= trade_date, TradeModel.tradeDate < next_day))
        except ValueError:
            pass

    return query